Excel Question Bank: Volume 3 by Mohit Kukreja

Author:Mohit Kukreja
Language: eng
Format: azw3, epub
Published: 2016-07-07T07:00:00+00:00

Debug.Print FolderLocation & "\" & strFileName

Module2.Macro1 FolderLocation & "\" & strFileName

strFileName = Dir() 'ERROR is Here

Loop

End Sub

Sub Macro1(sFPFN As String)

Dim x As String, textline As String Debug.Print sFPFN

Open sFPFN For Input As #1

Do Until EOF(1)

Line Input #1, textline

x = x & textline

Loop

Close #1

MsgBox x

End Sub

I passed the folder and filename name across as a string-type parameter. Also, I don't know why you had the extra spaces in (FolderLocation & " \ * " ; I tightened that up. There were a few misspellings and undeclared variables; these can be avoided with Option Explicit¹ at the top of the module code sheet. Get into the practise of standard indentation with your code. It certainly improves readability if nothing else.

¹ Setting Require Variable Declaration within the VBE's Tools ► Options ► Editor property page will put the Option Explicit statement at the top of each newly created code sheet. This will avoid silly coding mistakes like misspellings as well as influencing you to use the correct variable type in the variable declaration. Variables created on-the-fly without declaration are all of the variant/object type. Using Option Explicit is widely considered 'best practice'.

Topic 554

Subject

Using a Script to hide rows in excel spreadsheet

Question

I have the following script in an Excel cell. I'm assuming this is a vba script.

{ if (value == "") {hideRow("34");Text_Boiler_Location(2)}

I've done some research and I'm not sure how to hide the active row without hardcoding the row id. I'm using a form building application that I can build custom reports. However if I change the form then it will throw of the row id in the script. I will many of these little scripts and I do not want to change the row id every time I change the form. So basically I just need to hide the active row if the value is an empty string without hardcoding it. Answer

Can try below

ActiveCell.EntireRow.Hidden=True

Answer

This is neither vb or vba. It is a script created by the form application I use. They do not have the ability at this time to identify the active row. It has to be hard coded. Thanks for everyone's response.

Topic 555

Subject

How to make cell range dynamic in DDE in SAS

Question

I am trying to format a weekly report using DDE in sas which opens a excel file kept on my desktop location. I want to format the font some of these cells. I have a total column which needs to be formatted every time the new data comes in. But every time this data would come out the range of the "Total" cell would be different. Please refer to the code i am writing

options noxsync noxwait;

filename sas2xl dde 'excel|system';

data null;

length fid rc start stop time 8;

fid=fopen('sas2xl','s');

if (fid le 0) then do;

rc=system('start excel');

start=datetime();

stop=start+10;

do while (fid le 0);

fid=fopen('sas2xl','s');

time=datetime();

if (time ge stop) then fid=1;

end;

end;

rc=fclose(fid);

run;

data null;

file sas2xl;

put '[open(myfilepath\file.xlsx")]';

run;

**I had created a macro using call symput and i am using it here for dynamically changing the row number** %macro xyz;

data null;

file sas2xl;

%do i= 1 %to 3;

put '[error(false)]';

put '[workbook.activate("Sheet1")]';

put '[select("r&&rownum&i..c1")]';

put '[format.font("Arial",true,false,false,false,1,false,false)]'; put '[column.width(0,"c1:c6",false,3)]'; %end;

run;

ERROR

The code is not working and give me a error like this DDE session not ready.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.